/*
 * @(#)AbstractSetupOptionDAObject.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.setup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.setup.bean.SetupOption;

/**
 *  OptionsSetup main DAO
 *
 *  @author           Kennedy Lam
 *  @company          DCIVision Limited
 *  @creation date    28/07/2003
 *  @version          $Revision: 1.6.42.2 $
 */
public class AbstractSetupOptionDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.6.42.2 $";


  protected String sOptionNameCol;
  protected String sOptionCodeCol;
  protected String sDefaultOrder;
  protected boolean bHasOptionCode;

  /**
   * Default Constructor
   */
  public AbstractSetupOptionDAObject() {
    super();
  }

  /**
   * @param dbConn
   */
  public AbstractSetupOptionDAObject(Connection dbConn) {
    super(dbConn);
  }

  /**
   * @param sessionContainer
   * @param dbConn
   */
  public AbstractSetupOptionDAObject(
    SessionContainer sessionContainer,
    Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  public boolean hasOptionCode() {
    return bHasOptionCode;
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#getByID(java.lang.Integer)
   */
  
  protected AbstractBaseObject getByID(Integer id)
    throws ApplicationException {

    //log.debug("DEBUG - AbstractSetupOptionDAObject.getByID()");

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    StringBuffer sql = new StringBuffer();

    synchronized (dbConn) {
      try {
        sql.append(SELECT_SQL_1);
        sql.append(sOptionNameCol);

        if ( bHasOptionCode ) {
          sql.append(",").append(sOptionCodeCol);
        }

        sql.append(SELECT_SQL_2);
        sql.append(this.baseTableName);
        sql.append(SELECT_SQL_3);

        pstmt = dbConn.prepareStatement(sql.toString());
        this.setPrepareStatement(pstmt, 1, id);

        rs = pstmt.executeQuery();
        if ( rs.next() ) {
          // record exist
          SetupOption setupOption = new SetupOption();
          setupOption.setID(getResultSetInteger(rs, "ID"));
          setupOption.setOptionName(getResultSetString(rs, sOptionNameCol));

          if ( bHasOptionCode ) {
            setupOption.setOptionCode(getResultSetString(rs, sOptionCodeCol));
          }

          setupOption.setStatus(getResultSetString(rs, "STATUS"));
          setupOption.setDisplaySeq(getResultSetInteger(rs, "DISPLAY_SEQ"));
          setupOption.setSysInd(getResultSetString(rs, "SYS_IND"));
          setupOption.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          setupOption.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          setupOption.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          setupOption.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          setupOption.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          setupOption.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          setupOption.setCreatorName(UserInfoFactory.getUserFullName(setupOption.getCreatorID()));
          setupOption.setUpdaterName(UserInfoFactory.getUserFullName(setupOption.getUpdaterID()));

          return setupOption;
        }
        else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      }
      catch (ApplicationException appEx) {
        throw appEx;
      }
      catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      }
      catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      }
      finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    }
  }

  public synchronized List getListByRecordStatusAndStatus(String recordStatus, String status)
    throws ApplicationException {

    //log.debug("DEBUG - AbstractSetupOptionDAObject.getListByRecordStatusAndStatus()");

    ArrayList result = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    if ( dbConn == null )
    {
      //log.debug("DEBUG - dbConn is null");
    }

    synchronized (dbConn) {
      try {
        pstmt = dbConn.prepareStatement(getSelectStatement(recordStatus, status));
        if (!status.equals("*")) {
          if (!recordStatus.equals("*")) {
            this.setPrepareStatement(pstmt, 1, status);
            this.setPrepareStatement(pstmt, 2, recordStatus);
          }
          else {
            this.setPrepareStatement(pstmt, 1, status);
          }
        }
        else {
          if (!recordStatus.equals("*")) {
            this.setPrepareStatement(pstmt, 1, recordStatus);
          }
        }
        rs = pstmt.executeQuery();

        //log.debug("DEBUG - executeQuery()");

        while ( rs.next() ) {
          SetupOption setupOption = new SetupOption();
          setupOption.setID(getResultSetInteger(rs, "ID"));
          setupOption.setOptionName(getResultSetString(rs, sOptionNameCol));

          if ( bHasOptionCode ) {
            setupOption.setOptionCode(getResultSetString(rs, sOptionCodeCol));
          }

          setupOption.setStatus(getResultSetString(rs, "STATUS"));
          setupOption.setDisplaySeq(getResultSetInteger(rs, "DISPLAY_SEQ"));
          setupOption.setSysInd(getResultSetString(rs, "SYS_IND"));
          setupOption.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          setupOption.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          setupOption.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          setupOption.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          setupOption.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          setupOption.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          setupOption.setCreatorName(UserInfoFactory.getUserFullName(setupOption.getCreatorID()));
          setupOption.setUpdaterName(UserInfoFactory.getUserFullName(setupOption.getUpdaterID()));

          result.add(setupOption);
        }

      }
      catch (ApplicationException appEx) {
        throw appEx;
      }
      catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      }
      catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      }
      finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    }
    return result;
  }

  
  public synchronized AbstractBaseObject getObjectByID(Integer id) throws ApplicationException {
    return(this.getByID(id));
  }

  /*
   * unused method
   */
  public synchronized List getListByOptionName(String optionName)
    throws ApplicationException {
    ArrayList result = new ArrayList();
    StringBuffer sql = new StringBuffer();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    synchronized (dbConn) {
      try {
        sql.append(SELECT_SQL_1);
        sql.append(sOptionNameCol);

        if ( bHasOptionCode ) {
          sql.append(",").append(sOptionCodeCol);
        }

        sql.append(SELECT_SQL_2);
        sql.append(this.baseTableName);
        sql.append(" WHERE ").append(sOptionNameCol).append(" = ?");

        pstmt = dbConn.prepareStatement(sql.toString());
        this.setPrepareStatement(pstmt, 1, optionName);

        rs = pstmt.executeQuery();
        while ( rs.next() ) {
          SetupOption setupOption = new SetupOption();
          setupOption.setID(getResultSetInteger(rs, "ID"));
          setupOption.setOptionName(getResultSetString(rs, sOptionNameCol));

          if ( bHasOptionCode ) {
            setupOption.setOptionCode(getResultSetString(rs, sOptionCodeCol));
          }

          setupOption.setStatus(getResultSetString(rs, "STATUS"));
          setupOption.setDisplaySeq(getResultSetInteger(rs, "DISPLAY_SEQ"));
          setupOption.setSysInd(getResultSetString(rs, "SYS_IND"));
          setupOption.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          setupOption.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));

          setupOption.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          setupOption.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          setupOption.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          setupOption.setCreatorName(UserInfoFactory.getUserFullName(setupOption.getCreatorID()));
          setupOption.setUpdaterName(UserInfoFactory.getUserFullName(setupOption.getUpdaterID()));

          result.add(setupOption);
        }
      }
      catch (ApplicationException appEx) {
        throw appEx;
      }
      catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      }
      catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      }
      finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    }

    return result;
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#getList(com.dcivision.framework.web.AbstractSearchForm)
   */
  
  protected List getList(AbstractSearchForm searchForm)
    throws ApplicationException {
    
    return null;
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#insert(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected AbstractBaseObject insert(AbstractBaseObject obj)
    throws ApplicationException {

    //log.debug("DEBUG - AbstractSetupOptionDAObject.insert()");
    PreparedStatement pstmt = null;
    SetupOption tmpSetupOption = (SetupOption) ((SetupOption) obj).clone();

    synchronized(dbConn) {
      try {
        Integer nextID = this.getNextPrimaryID();
        //log.debug("DEBUG - NEXTID:" + nextID);
        pstmt = dbConn.prepareStatement(getInsertStatement());
        Timestamp currTime = Utility.getCurrentTimestamp();
        int i = 1;

        setPrepareStatement(pstmt, i++, nextID);
        setPrepareStatement(pstmt, i++, tmpSetupOption.getOptionName());
        if ( bHasOptionCode ) {
          setPrepareStatement(pstmt, i++, tmpSetupOption.getOptionCode());
        }
        setPrepareStatement(pstmt, i++, tmpSetupOption.getStatus());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getDisplaySeq());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getSysInd());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getRecordStatus());
        setPrepareStatement(pstmt, i++, new Integer(0));
        setPrepareStatement(pstmt, i++, sessionContainer.getUserRecordID());
        setPrepareStatement(pstmt, i++, currTime);
        setPrepareStatement(pstmt, i++, sessionContainer.getUserRecordID());
        setPrepareStatement(pstmt, i++, currTime);

        pstmt.executeUpdate();

        tmpSetupOption.setID(nextID);
        tmpSetupOption.setCreatorID(sessionContainer.getUserRecordID());
        tmpSetupOption.setCreateDate(currTime);
        tmpSetupOption.setUpdaterID(sessionContainer.getUserRecordID());
        tmpSetupOption.setUpdateDate(currTime);
        tmpSetupOption.setCreatorName(UserInfoFactory.getUserFullName(tmpSetupOption.getCreatorID()));
        tmpSetupOption.setUpdaterName(UserInfoFactory.getUserFullName(tmpSetupOption.getUpdaterID()));

        return(tmpSetupOption);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    } // end sync
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#update(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected AbstractBaseObject update(AbstractBaseObject obj)
    throws ApplicationException {

    //log.debug("DEBUG - AbstractSetupOptionDAObject.update()");

    PreparedStatement pstmt = null;
    SetupOption tmpSetupOption = (SetupOption) ((SetupOption) obj).clone();

    synchronized(dbConn) {
      try {
        pstmt = dbConn.prepareStatement(getUpdateStatement());
        Timestamp currTime = Utility.getCurrentTimestamp();
        int i = 1;

        setPrepareStatement(pstmt, i++, tmpSetupOption.getOptionName());
        if ( bHasOptionCode ) {
          setPrepareStatement(pstmt, i++, tmpSetupOption.getOptionCode());
        }
        setPrepareStatement(pstmt, i++, tmpSetupOption.getStatus());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getDisplaySeq());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getSysInd());
        //log.debug("Update Count: " + tmpSetupOption.getUpdateCount());
        setPrepareStatement(pstmt, i++, new Integer(tmpSetupOption.getUpdateCount().intValue() + 1));
        setPrepareStatement(pstmt, i++, sessionContainer.getUserRecordID());
        setPrepareStatement(pstmt, i++, currTime);
        setPrepareStatement(pstmt, i++, tmpSetupOption.getID());
        setPrepareStatement(pstmt, i++, tmpSetupOption.getUpdateCount());

        int updateCnt = pstmt.executeUpdate();

        if ( updateCnt == 0 ) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpSetupOption.setUpdaterID(sessionContainer.getUserRecordID());
          tmpSetupOption.setUpdateDate(currTime);
          tmpSetupOption.setUpdateCount(new Integer(tmpSetupOption.getUpdateCount().intValue() + 1));
          tmpSetupOption.setCreatorName(UserInfoFactory.getUserFullName(tmpSetupOption.getCreatorID()));
          tmpSetupOption.setUpdaterName(UserInfoFactory.getUserFullName(tmpSetupOption.getUpdaterID()));
          return(tmpSetupOption);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    } // end sync
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#delete(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected AbstractBaseObject delete(AbstractBaseObject obj)
    throws ApplicationException {

    //log.debug("DEBUG - AbstractSetupOptionDAObject.delete()");

    PreparedStatement pstmt = null;
    SetupOption tmpSetupOption = (SetupOption) ((SetupOption) obj).clone();

    synchronized(dbConn) {
      try {
        pstmt = dbConn.prepareStatement(getDeleteStatement());

        setPrepareStatement(pstmt, 1, tmpSetupOption.getID());
        setPrepareStatement(pstmt, 2, tmpSetupOption.getUpdateCount());

        int updateCnt = pstmt.executeUpdate();

        if ( updateCnt == 0 ) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpSetupOption);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { pstmt.close(); } catch (Exception ignore) {} finally { pstmt = null; }
      }
    } // end sync

  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#auditTrail(java.lang.String, com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected void auditTrail(String opMode, AbstractBaseObject obj)
    throws ApplicationException {

    SetupOption tmpSetupOption = (SetupOption) this.oldValue;
    Vector oldValues = new Vector();
    Vector newValues = new Vector();

    if ( tmpSetupOption != null )
    {
      oldValues.add(toAuditTrailValue(tmpSetupOption.getID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getOptionName()));
      if ( bHasOptionCode ) {
        oldValues.add(toAuditTrailValue(tmpSetupOption.getOptionCode()));
      }
      oldValues.add(toAuditTrailValue(tmpSetupOption.getStatus()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getDisplaySeq()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getSysInd()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdateDate()));
    }

    tmpSetupOption = (SetupOption) obj;

    if ( tmpSetupOption != null )
    {
      oldValues.add(toAuditTrailValue(tmpSetupOption.getID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getOptionName()));
      if ( bHasOptionCode ) {
        oldValues.add(toAuditTrailValue(tmpSetupOption.getOptionCode()));
      }
      oldValues.add(toAuditTrailValue(tmpSetupOption.getStatus()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getDisplaySeq()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getSysInd()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpSetupOption.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#initDBSetting()
   */
  
  protected void initDBSetting() {
    ////log.debug("AbstractSetupOptionDAObject.initDBSetting()");
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add(sOptionNameCol);

    if ( bHasOptionCode ) {
      this.vecDBColumn.add(sOptionCodeCol);
    }

    this.vecDBColumn.add("STATUS");
    this.vecDBColumn.add("DISPLAY_SEQ");
    this.vecDBColumn.add("SYS_IND");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("UPDATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#validateInsert(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected void validateInsert(AbstractBaseObject obj)
    throws ApplicationException {
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#validateUpdate(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected void validateUpdate(AbstractBaseObject obj)
    throws ApplicationException {
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#validateDelete(com.dcivision.framework.bean.AbstractBaseObject)
   */
  
  protected void validateDelete(AbstractBaseObject obj)
    throws ApplicationException {
  }

  /* (non-Javadoc)
   * @see com.dcivision.framework.dao.AbstractDAObject#getList()
   */
  
  protected List getList() throws ApplicationException {
    
    return null;
  }

  private String getSelectStatement(String recordStatus, String status)
  {
    StringBuffer sql = new StringBuffer(SELECT_SQL_1);
    sql.append(sOptionNameCol);

    if ( bHasOptionCode ) {
      sql.append(",").append(sOptionCodeCol);
    }

    sql.append(SELECT_SQL_2);
    sql.append(this.baseTableName);

    if (!status.equals("*")) {
      if (!recordStatus.equals("*")) {
        sql.append(" WHERE STATUS = ? AND RECORD_STATUS = ? ");
      }
      else {
        sql.append(" WHERE STATUS = ? ");
      }
    }
    else {
      if (!recordStatus.equals("*")) {
        sql.append(" WHERE RECORD_STATUS = ? ");
      }
    }
    sql.append(" ORDER BY DISPLAY_SEQ ");

    //log.debug("DEBUG - sql:" + sql.toString());

    return sql.toString();
  }

  public String getUpdateStatement()
  {
    StringBuffer sql = new StringBuffer(UPDATE_SQL_1);
    sql.append(this.baseTableName);
    sql.append(UPDATE_SQL_2);
    sql.append(sOptionNameCol);
    if ( bHasOptionCode ) {
      sql.append("=?, ").append(sOptionCodeCol);
    }

    sql.append(UPDATE_SQL_3);

    //log.debug("DEBUG - " + sql.toString());

    return sql.toString();
  }

  public String getInsertStatement()
  {
    StringBuffer sql = new StringBuffer(INSERT_SQL_1);
    sql.append(this.baseTableName);
    sql.append(INSERT_SQL_2);
    sql.append(sOptionNameCol);
    if ( bHasOptionCode ) {
      sql.append(", ").append(sOptionCodeCol);
    }
    sql.append(INSERT_SQL_3);

    if ( bHasOptionCode ) {
      sql.append(",?)");
    } else {
      sql.append(")");
    }

    //log.debug("DEBUG - " + sql.toString());

    return sql.toString();
  }

  public String getDeleteStatement()
  {
    StringBuffer sql = new StringBuffer(DELETE_SQL_1);
    sql.append(this.baseTableName);
    sql.append(DELETE_SQL_2);

    //log.debug("DEBUG - " + sql.toString());

    return sql.toString();
  }

  private static final String SELECT_SQL_1;
  private static final String SELECT_SQL_2;
  private static final String SELECT_SQL_3;
  private static final String UPDATE_SQL_1;
  private static final String UPDATE_SQL_2;
  private static final String UPDATE_SQL_3;
  private static final String INSERT_SQL_1;
  private static final String INSERT_SQL_2;
  private static final String INSERT_SQL_3;
  private static final String DELETE_SQL_1;
  private static final String DELETE_SQL_2;

  static
  {
    SELECT_SQL_1 = "SELECT ID, "; // append sOptionNameCol and/or sOptionCodeCol
    SELECT_SQL_2 = ", STATUS, DISPLAY_SEQ, SYS_IND, RECORD_STATUS, UPDATE_COUNT, CREATE_DATE, CREATOR_ID, UPDATE_DATE, UPDATER_ID FROM ";
                       // append baseTableName;
    SELECT_SQL_3 = " WHERE ID = ? ";

    UPDATE_SQL_1 = "UPDATE "; // append baseTableName;
    UPDATE_SQL_2 = " SET "; // append sOptionNameCol and/or sOptionCodeCol
    UPDATE_SQL_3 = "=?, STATUS=?, DISPLAY_SEQ=?, SYS_IND=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? WHERE ID=? AND UPDATE_COUNT=?";

    INSERT_SQL_1 = "INSERT INTO "; // append baseTableName;
    INSERT_SQL_2 = " (ID, "; // append sOptionNameCol and/or sOptionCodeCol
    INSERT_SQL_3 = new StringBuffer(", STATUS, DISPLAY_SEQ, SYS_IND, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE)")
      .append(" VALUES (?,?,?,?,?,?,?,?,?,?,?").toString(); // append ) or ,?)

    DELETE_SQL_1 = "DELETE FROM "; // append baseTableName
    DELETE_SQL_2 = " WHERE ID = ? AND UPDATE_COUNT = ?";
  }
}
